
$XMLDataLoadClass = New-PSClass AssureDbFramework -Inherit $PSSqlCommandClass {

	Constructor {
		Base $Args[0] $Args[1]

	}

  method Setup {
		$this.Command.CommandType = [System.Data.CommandType]::Text
		$this.ExecuteNonQuery( ($Private.SetupSQL) )
	}
  method Cleanup {
		$this.Command.CommandType = [System.Data.CommandType]::Text
		$this.ExecuteNonQuery( ($Private.CleanupSQL) )
	}
	
	method LoadTable {
		$Schema = $Args[0]
		$Table = $Args[1]
		$DataFileName = $Args[2]
		
    $XmlData = Get-Content $DataFileName
    $sb = New-Object system.Text.StringBuilder
    [void] $sb.AppendLine("<ROOT>")
    foreach ( $line in $XmlData )
    {
      [void] $sb.AppendLine($line)
    }
    [void] $sb.AppendLine("</ROOT>")

		$this.Command.CommandType = [System.Data.CommandType]::StoredProcedure
		
		$this.Command.Parameters.Clear()
		[void] $this.Command.Parameters.Add("@TableSchema",$Schema)
		[void] $this.Command.Parameters.Add("@TableName",$Table)
		[void] $this.Command.Parameters.Add("@IncludeTimestamp",0)
		[void] $this.Command.Parameters.Add("@xml",$sb.ToString())

		$this.Command.CommandText = "#LoadXML"
		
		try {
			[void] $this.Command.ExecuteNonQuery()
		} -catch {
			Log-Error "Error while loading Table $($schema).$Table from $DataFileName"
			Log-Error "$_"
			Throw "Error while loading Table $($schema).$Table from $DataFileName"
		}
	}

	method DisableFKs {
		$Schema = $Args[0]
		$Table = $Args[1]
		try {
			$private.InvokeSetFKsForTable($Schema,$Table,0)
		} -catch {
			Log-Error "Error while disabling FKs to table $Schema.$Table"
			Log-Message "-----------------"
		  Throw $_
		}
	}

	method EnableFKs {
		$Schema = $Args[0]
		$Table = $Args[1]
		try {
			$private.InvokeSetFKsForTable($Schema,$Table,1)
		} -catch {
			Log-Error "Error while enabling FKs to table $Schema.$Table"
			Log-Message "-----------------"
		  Throw $_
		}
	}
	
	method -private InvokeSetFKsForTable {
		$Schema = $Args[0]
		$Table = $Args[1]
		$EnableFK = $Args[2]
		$this.Command.CommandType = [System.Data.CommandType]::StoredProcedure
		
		$this.Command.Parameters.Clear()
		[void] $this.Command.Parameters.Add("@TargetTableSchema",$null)
		$this.Command.Parameters["@TargetTableSchema"].Value = $Schema
		
		[void] $this.Command.Parameters.Add("@TargetTableName",$null)
		$this.Command.Parameters["@TargetTableName"].Value = $Table
		
		[void] $this.Command.Parameters.Add("@EnableFK",$null)
		$this.Command.Parameters["@EnableFK"].Value = $EnableFK
		
		$this.Command.CommandText = "#SetFKsForTable"
		try {
			[void] $this.Command.ExecuteNonQuery()
		} -catch {
			Log-Error $_
		  Throw $_
		}
	}

#region SetupSQL
	note -private SetupSQL @"
BEGIN TRANSACTION;
-- ****************************************************************************************************************

IF OBJECT_ID('tempdb..#SetFKsForTable') IS NOT NULL
DROP PROC #SetFKsForTable
go
-- =============================================
-- Author:		cash.foley@ps.net
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [#SetFKsForTable]
(
	@TargetTableSchema varchar(100),
	@TargetTableName varchar(100),
	@EnableFK bit
)
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @FK_CHECK varchar(50)

	IF @EnableFK = 1 
		SET @FK_CHECK = 'WITH CHECK CHECK'
	ELSE
		SET @FK_CHECK = 'NOCHECK'

	DECLARE @Table_Schema varchar(100)
	DECLARE @Table_Name varchar(100)
	DECLARE @Constraint_Name varchar(100)

	DECLARE @SQL varchar(2000)

	DECLARE FK_Cursor CURSOR FOR
		SELECT QUOTENAME(ctu.Table_Schema)    AS Table_Schema
			 , QUOTENAME(ctu.Table_Name)      AS Table_Name
			 , QUOTENAME(rc.Constraint_Name)  AS Constraint_Name
		  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
		  JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
			ON tc.Constraint_Name = rc.Unique_Constraint_Name
		   AND tc.Constraint_Schema = rc.Constraint_Schema
		  JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu
			ON rc.Constraint_Name = ctu.Constraint_Name
		   AND rc.Constraint_Schema = ctu.Constraint_Schema
		 where tc.table_schema = @TargetTableSchema
		   and tc.table_name = @TargetTableName

	OPEN FK_Cursor
	FETCH NEXT FROM FK_Cursor
	INTO @Table_Schema, @Table_Name, @Constraint_Name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SQL = 'ALTER TABLE ' + @Table_Schema + '.' + @Table_Name + ' ' + @FK_CHECK + ' CONSTRAINT ' + @Constraint_Name

		PRINT @SQL
		EXECUTE (@SQL)

		FETCH NEXT FROM FK_Cursor
		INTO @Table_Schema, @Table_Name, @Constraint_Name
	END;
	CLOSE FK_Cursor;
	DEALLOCATE FK_Cursor;
END

GO

-- ****************************************************************************************************************

IF OBJECT_ID('tempdb..#LoadXML') IS NOT NULL
DROP PROC #LoadXML
GO

CREATE PROCEDURE [#LoadXML]
  ( @TableSchema varchar(100)
  , @TableName varchar(100)
  , @IncludeTimestamp bit
  , @xml text
  )
AS
BEGIN
	---------------------------------------------------------------------------
	-- Build Strings with Column Names and Column Definitions using Meta Data
	---------------------------------------------------------------------------
	DECLARE @ColumnName varchar(100)
	DECLARE @DataType varchar(100)
	DECLARE @DataMaxLength varchar(100)

	DECLARE @SQL varchar(2000)

	DECLARE Column_Cursor CURSOR FOR
	WITH TableColumn 
	   ( ColumnName
	   , OrdinalPosition
	   , DataType
	   , DataMaxLength
	   ) AS (
	  select Column_Name
		   , Ordinal_Position
		   , Data_Type
		   , Character_Maximum_Length
		from INFORMATION_SCHEMA.COLUMNS
	   where Table_Schema = @TableSchema
		 and Table_Name = @TableName
	  )
	SELECT ColumnName
		 , DataType
		 , CASE 
		     WHEN (DataMaxLength = -1) THEN '(max)'
		     WHEN (DataMaxLength IS NOT NULL) THEN '(' + CAST (DataMaxLength AS varchar) + ')'
		     ELSE ''
		   END
	  FROM TableColumn
	 ORDER BY OrdinalPosition

	OPEN Column_Cursor
	FETCH NEXT FROM Column_Cursor
	INTO @ColumnName, @DataType, @DataMaxLength

	DECLARE @Delim varchar(3)
	DECLARE @ColumnNames varchar(2000)
	DECLARE @ColumnDef varchar(2000)
	SET @Delim = ''
	SET @ColumnNames = ''
	SET @ColumnDef = ''

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ((@IncludeTimestamp = 1) OR (@DataType <> 'TIMESTAMP'))
		BEGIN
			SET @ColumnNames = @ColumnNames + @Delim + @ColumnName
			SET @ColumnDef = @ColumnDef + @Delim + @ColumnName + ' ' + @DataType + @DataMaxLength
			SET @Delim = ', '
		END

		FETCH NEXT FROM Column_Cursor
		INTO @ColumnName, @DataType, @DataMaxLength
	END;

	CLOSE Column_Cursor;
	DEALLOCATE Column_Cursor;

	---------------------------------------------------------------------------
	-- Build Dynamic SQL to insert XML Doc into target Table
	---------------------------------------------------------------------------
	DECLARE @idoc int

	EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

	DECLARE @HasIdentity int

	SELECT @HasIdentity=Count(*)
	  FROM sys.tables AS t
	  JOIN sys.identity_columns c ON t.object_id = c.object_id
	 WHERE t.name = @TableName
	   AND SCHEMA_NAME(schema_id) = @TableSchema

	DECLARE @FullTableRef varchar(150)
	SET @FullTableRef = '[' + @TableSchema + '].[' + @TableName + ']'

	SET @SQL = 'BEGIN
	SET NOCOUNT ON'

	IF @HasIdentity > 0
		SET @SQL = @SQL + '
	SET IDENTITY_INSERT ' + @FullTableRef + ' ON'

	SET @SQL = @SQL + '
	DELETE FROM ' + @FullTableRef

	SET @SQL = @SQL + '
    DECLARE @docID int
    SET @docID = ' + CAST(@idoc as VARCHAR) + '
	INSERT 
	  INTO ' + @FullTableRef + '
		 ( ' + @ColumnNames + ' )
	SELECT ' + @ColumnNames + '
	  FROM OPENXML (@docID, ''/ROOT/' + @TableSchema + '.' + @TableName + ''',1) 
	  WITH (' + @ColumnDef   + ')'

	IF @HasIdentity > 0
		SET @SQL = @SQL + '
	SET IDENTITY_INSERT ' + @FullTableRef + ' OFF'

	SET @SQL = @SQL + '
END'

	EXEC (@SQL)
	PRINT @SQL

	EXEC sp_xml_removedocument @idoc

END
GO

COMMIT TRANSACTION;
"@
#endregion

#region CleanupSQL

	note -private CleanupSQL @"
IF OBJECT_ID('tempdb..#SetFKsForTable') IS NOT NULL
DROP PROC #SetFKsForTable

IF OBJECT_ID('tempdb..#LoadXML') IS NOT NULL
DROP PROC #LoadXML
"@
#endregion

}

































































































